﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SqlOrm.Xml;
using System.IO;

namespace SqlOrm.ScriptBuilder
{
    public class SQL2008R2ScriptBuilder: IScriptBuilder
    {
        private DataDictionary _ddl;
        public DataDictionary DDL { get { return _ddl; } set { _ddl = value; } }

        public void UseDatabase(StreamWriter sw, int d)
        {
            sw.WriteLine(string.Format("use [{0}]", _ddl.Settings.Database.name));
            sw.WriteLine("GO");
        }

        public void DropConstraints(StreamWriter sw, int d)
        {
            var def = _ddl.Definition;
            foreach (var table in def.Schema.Table)
            {
                foreach (var f in table.Field)
                {
                    if (f.FK != null)
                    {
                        string fkname = (!string.IsNullOrEmpty(f.FK.fkname)) ? f.FK.fkname : string.Format("FK_{0}_{1}", table.ClassName, def.Schema.GetTable(f.FK).ClassName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey {0}    Script Date: {1} ******/", fkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{0}') AND parent_object_id = OBJECT_ID(N'{1}'))", fkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE {0} DROP CONSTRAINT [{1}]", table.FullyQualifiedSqlName, fkname));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                    if (!string.IsNullOrEmpty(f.EnumName))
                    {
                        string efkname = string.Format("FK_{0}_{1}", table.ClassName, f.EnumLookupTableName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey [{0}]    Script Date: {1} ******/", efkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", efkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE {0} DROP CONSTRAINT [{1}]", table.FullyQualifiedSqlName, efkname));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                }
            }
        }

        public void DropTables(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [dbo].[{0}]    Script Date: {1} ******/", table.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                sw.WriteLine(Indent(d) + "IF EXISTS (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '{0}' and TABLE_NAME = '{1}')".F(table.schema, table.name));
                //sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U'))", table.FullyQualifiedSqlName));
                sw.WriteLine(Indent(d) + string.Format("DROP TABLE {0}", table.FullyQualifiedSqlName));
                sw.WriteLine(Indent(d) + string.Format("GO"));
            }
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                foreach (var f in table.Field.Where(x => !string.IsNullOrEmpty(x.EnumName)))
                {
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [dbo].[{0}]    Script Date: {1} ******/", f.EnumLookupTableName, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d) + string.Format("DROP TABLE [dbo].[{0}]", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d) + string.Format("GO"));
                }
            }
        }

        public void CreateTables(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [dbo].[{0}]    Script Date: {1} ******/", table.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                sw.WriteLine(Indent(d) + string.Format("SET ANSI_NULLS ON"));
                sw.WriteLine(Indent(d) + string.Format("GO"));
                sw.WriteLine(Indent(d) + string.Format("SET QUOTED_IDENTIFIER ON"));
                sw.WriteLine(Indent(d) + string.Format("GO"));
                sw.WriteLine(Indent(d) + string.Format("SET ANSI_PADDING ON"));
                sw.WriteLine(Indent(d) + string.Format("GO"));
                sw.WriteLine(Indent(d++) + string.Format("CREATE TABLE {0}(", table.FullyQualifiedSqlName));
                foreach (var f in table.Field)
                {
                    sw.WriteLine(Indent(d) + "[{0}] {1} {2},", f.name, f.SqlDeclaredType, (f.nulls) ? "NULL" : "NOT NULL");
                }
                foreach (var k in table.Field.Where(x => x.key))
                {
                    sw.WriteLine(Indent(d) + string.Format("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED", table.ClassName));
                    sw.WriteLine(Indent(d++) + string.Format("("));
                    sw.WriteLine(Indent(d--) + string.Format("[{0}] ASC", k.name));
                    sw.WriteLine(Indent(d) + string.Format(") WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"));
                }
                foreach (var f in table.Field)
                {
                    if (f.FK != null)
                    {

                    }
                }
                sw.WriteLine(Indent(--d) + string.Format(") ON [PRIMARY]"));
                sw.WriteLine(Indent(d) + string.Format("GO"));
            }
            // enum tables
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                foreach (var f in table.Field.Where(x => !string.IsNullOrEmpty(x.EnumName)))
                {
                    int maxTextSize = f.Enum.Max(x => x.desc.Length);
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Table [dbo].[{0}]    Script Date: {1} ******/", f.EnumLookupTableName, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d) + string.Format("SET ANSI_NULLS ON"));
                    sw.WriteLine(Indent(d) + string.Format("GO"));
                    sw.WriteLine(Indent(d) + string.Format("SET QUOTED_IDENTIFIER ON"));
                    sw.WriteLine(Indent(d) + string.Format("GO"));
                    sw.WriteLine(Indent(d) + string.Format("SET ANSI_PADDING ON"));
                    sw.WriteLine(Indent(d) + string.Format("GO"));
                    sw.WriteLine(Indent(d++) + string.Format("CREATE TABLE [dbo].[{0}](", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d) + string.Format("[id] {0} NOT NULL,", f.SqlDataType));
                    sw.WriteLine(Indent(d) + string.Format("[desc] varchar({0}) NOT NULL", maxTextSize.ToString()));
                    sw.WriteLine(Indent(d) + string.Format("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED", f.EnumLookupTableName));
                    sw.WriteLine(Indent(d++) + string.Format("("));
                    sw.WriteLine(Indent(d--) + string.Format("[{0}] ASC", "id"));
                    sw.WriteLine(Indent(d) + string.Format(") WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"));
                    sw.WriteLine(Indent(--d) + string.Format(") ON [PRIMARY]"));
                    sw.WriteLine(Indent(d) + string.Format("GO"));

                    foreach (var e in f.Enum)
                    {
                        string enumVal = string.Empty;
                        if (!string.IsNullOrEmpty(e.value))
                            enumVal = e.value;
                        else
                            enumVal = (Array.IndexOf(f.Enum, e) + 1).ToString();
                        sw.WriteLine(Indent(d) + string.Format("insert into [dbo].[{0}] ( [id], [desc] ) values ( {1}, '{2}' )", f.EnumLookupTableName, enumVal, e.desc));
                    }
                }
            }
        }

        public void CreateConstraints(StreamWriter sw, int d)
        {
            var schema = _ddl.Definition.Schema;
            foreach (var table in schema.Table)
            {
                foreach (var f in table.Field)
                {
                    if (f.FK != null)
                    {
                        string fkname = (!string.IsNullOrEmpty(f.FK.fkname)) ? f.FK.fkname : string.Format("FK_{0}_{1}", table.ClassName, schema.GetTable(f.FK).ClassName);
                        //string fkname = string.Format("FK_{0}_{1}", table.ClassName, schema.GetTable(f.FK).ClassName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey {0}    Script Date: {1} ******/", fkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        sw.WriteLine(Indent(d) + string.Format("IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{0}') AND parent_object_id = OBJECT_ID(N'{1}'))", fkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE {0}  WITH CHECK ADD  CONSTRAINT [{1}] FOREIGN KEY([{2}])", table.FullyQualifiedSqlName, fkname, f.name));
                        sw.WriteLine(Indent(d) + string.Format("REFERENCES {0} ([{1}])", f.FK.table, f.FK.column));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                        sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", fkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE {0} CHECK CONSTRAINT [{1}]", table.FullyQualifiedSqlName, fkname));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                    if (!string.IsNullOrEmpty(f.EnumName))
                    {
                        string efkname = string.Format("FK_{0}_{1}", table.ClassName, f.EnumLookupTableName);
                        sw.WriteLine(Indent(d) + string.Format("/****** Object:  ForeignKey [{0}]    Script Date: {1} ******/", efkname, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                        sw.WriteLine(Indent(d) + string.Format("IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", efkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE {0}  WITH CHECK ADD  CONSTRAINT [{1}] FOREIGN KEY([{2}])", table.FullyQualifiedSqlName, efkname, f.name));
                        sw.WriteLine(Indent(d) + string.Format("REFERENCES [dbo].[{0}] ([{1}])", f.EnumLookupTableName, "id"));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                        sw.WriteLine(Indent(d) + string.Format("IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND parent_object_id = OBJECT_ID(N'{1}'))", efkname, table.FullyQualifiedSqlName));
                        sw.WriteLine(Indent(d) + string.Format("ALTER TABLE {0} CHECK CONSTRAINT [{1}]", table.FullyQualifiedSqlName, efkname));
                        sw.WriteLine(Indent(d) + string.Format("GO"));
                    }
                }
            }
        }

        public void DropProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                if (table.Field.Any(f => f.key))
                {
                    string getName = string.Format("Get{0}", table.ClassName);
                    DropProc(sw, getName, d);
                }
                if (table.upsert)
                {
                    string upsName = string.Format("Upsert{0}", table.ClassName);
                    DropProc(sw, upsName, d);
                }
                if (table.insert)
                {
                    string insName = string.Format("Insert{0}", table.ClassName);
                    DropProc(sw, insName, d);
                }

                if (!string.IsNullOrEmpty(table.search))
                {
                    string[] patterns = table.search.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var pattern in patterns)
                    {
                        string[] sname = pattern.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                        List<Field> searchFields = new List<Field>();
                        foreach (var s in sname)
                        {
                            Field f = table.Field.Where(x => x.name == s).FirstOrDefault();
                            if (f != null)
                                searchFields.Add(f);
                        }
                        string procArgsName = string.Empty;
                        Array.ForEach(searchFields.ToArray(), x => procArgsName += x.title);
                        string methodName = string.Format("Search{0}By{1}", table.ClassName, procArgsName);

                        DropProc(sw, methodName, d);
                    }
                }
                if (table.all)
                {
                    DropProc(sw, "All" + table.name, d);
                }

            }
            if (_ddl.Definition.Procedures != null)
            {
                foreach (var proc in _ddl.Definition.Procedures)
                {
                    DropProc(sw, proc.name, d);
                }
            }
        }

        private void DropProc(StreamWriter sw, string procname, int d)
        {
            sw.WriteLine(string.Format(Indent(d) + "/****** Object:  StoredProcedure {0}    Script Date: {1} ******/", "Upsert" + procname, DateTime.Now.ToString("YYYY-MM-dd HH:mm:ss")));
            sw.WriteLine(string.Format(Indent(d++) + "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))", procname));
            sw.WriteLine(string.Format(Indent(d) + "DROP PROCEDURE [dbo].[{0}]", procname));
            sw.WriteLine(string.Format(Indent(--d) + "GO"));
        }

        public void CreateUpsertProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                if (table.upsert)
                {
                    sw.WriteLine("SET ANSI_NULLS ON");
                    sw.WriteLine("GO");
                    sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                    sw.WriteLine("GO");
                    sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE [dbo].[{0}]", "Upsert" + table.name));
                    foreach (var f in table.Field)
                    {
                        sw.Write(Indent(d) + "@{0} {1}", f.name, f.SqlDeclaredType);
                        if (Array.IndexOf(table.Field, f) != table.Field.Count() - 1)
                            sw.WriteLine(",");
                        else
                            sw.WriteLine();
                    }
                    sw.WriteLine(Indent(--d) + "AS");
                    sw.WriteLine(Indent(d++) + "BEGIN");
                    sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                    sw.WriteLine(Indent(d++) + string.Format("if exists (select * from [{0}] where {1}) begin", table.name, KeyList(table, " and ")));
                    sw.WriteLine(Indent(d) + string.Format("update {0} set {1} where {2}", table.FullyQualifiedSqlName, NonKeyList(table, ", "), KeyList(table, " and ")));
                    sw.WriteLine(Indent(--d) + "end");
                    sw.WriteLine(Indent(d++) + "else begin");
                    sw.Write(Indent(d) + string.Format("insert into {0} ({1}) values ({2})", table.FullyQualifiedSqlName, table.Field.SepList(f => "[" + f.name + "]", f => ", "), table.Field.SepList(f => "@" + f.name, f => ", ")));
                    sw.WriteLine(Indent(--d) + "end");
                    sw.WriteLine(Indent(--d) + "end");
                    sw.WriteLine(Indent(d) + "GO");
                }
            }
        }

        public void CreateInsertProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.insert))
            {
                sw.WriteLine("SET ANSI_NULLS ON");
                sw.WriteLine("GO");
                sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                sw.WriteLine("GO");
                sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE [{0}].[{1}]", table.schema, "Insert" + table.name));
                sw.WriteLine(Indent(d) + table.Field.SepList(f => "@{0} {1}".F(f.name, f.SqlDeclaredType), f => ",\r\n" + (Indent(d))));
                sw.WriteLine(Indent(--d) + "AS");
                sw.WriteLine(Indent(d++) + "BEGIN");
                sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                sw.WriteLine(Indent(d) + string.Format("insert into {0} ({1}) values ({2})", table.FullyQualifiedSqlName, table.Field.SepList(f => "[" + f.name + "]", f => ", "), table.Field.SepList(f => "@" + f.name, f => ", ")));
                sw.WriteLine(Indent(--d) + "end");
                sw.WriteLine(Indent(d) + "GO");
            }
        }

        public void CreateGetProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                sw.WriteLine("SET ANSI_NULLS ON");
                sw.WriteLine("GO");
                sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                sw.WriteLine("GO");
                sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE [dbo].[{0}]", "Get" + table.name));
                foreach (var f in table.Field.Where(x => x.key))
                {
                    sw.Write(Indent(d) + "@{0} {1}", f.name, f.SqlDeclaredType);
                    if (Array.IndexOf(table.Field.Where(x => x.key).ToArray(), f) != table.Field.Where(x => x.key).Count() - 1)
                        sw.WriteLine(",");
                    else
                        sw.WriteLine();
                }
                sw.WriteLine(Indent(--d) + "AS");
                sw.WriteLine(Indent(d++) + "BEGIN");
                sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                sw.WriteLine(Indent(d) + "select * from [{0}] where {1}", table.name, KeyList(table, " and "));
                sw.WriteLine(Indent(--d) + "END");
                sw.WriteLine(Indent(d) + "GO");
            }
        }

        public void CreateDeleteProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.delete))
            {
                sw.WriteLine("SET ANSI_NULLS ON");
                sw.WriteLine("GO");
                sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                sw.WriteLine("GO");
                sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE [dbo].[{0}]", "Delete" + table.name));
                foreach (var f in table.Field.Where(x => x.key))
                {
                    sw.Write(Indent(d) + "@{0} {1}", f.name, f.SqlDeclaredType);
                    if (Array.IndexOf(table.Field.Where(x => x.key).ToArray(), f) != table.Field.Where(x => x.key).Count() - 1)
                        sw.WriteLine(",");
                    else
                        sw.WriteLine();
                }
                sw.WriteLine(Indent(--d) + "AS");
                sw.WriteLine(Indent(d++) + "BEGIN");
                sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                sw.WriteLine(Indent(d) + "delete from [{0}] where {1}", table.name, KeyList(table, " and "));
                sw.WriteLine(Indent(--d) + "END");
                sw.WriteLine(Indent(d) + "GO");
            }
        }

        public void CreateSearchProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                if (!string.IsNullOrEmpty(table.search))
                {
                    string[] patterns = table.search.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var pattern in patterns)
                    {
                        string[] sname = pattern.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                        List<Field> searchFields = new List<Field>();
                        foreach (var s in sname)
                        {
                            Field f = table.Field.Where(x => x.name == s).FirstOrDefault();
                            if (f != null)
                                searchFields.Add(f);
                        }
                        string procArgsName = string.Empty;
                        Array.ForEach(searchFields.ToArray(), x => procArgsName += x.title);
                        string methodName = string.Format("Search{0}By{1}", table.ClassName, procArgsName);

                        sw.WriteLine("SET ANSI_NULLS ON");
                        sw.WriteLine("GO");
                        sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                        sw.WriteLine("GO");
                        sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE [dbo].[{0}]", methodName));
                        sw.WriteLine(Indent(d++) + searchFields.SepList(f => "@{0} {1}".F(f.name, f.SqlDeclaredType), f => ",\r\n"));
                        Indent(d--);
                        sw.WriteLine(Indent(--d) + "AS");
                        sw.WriteLine(Indent(d++) + "BEGIN");
                        sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                        sw.WriteLine(Indent(d) + "select * from [{0}] where {1}", table.name, searchFields.SepList(f => "[{0}] = @{0}".F(f.name), f => ", "));

                        if (table.Sorting != null && table.Sorting.SortField.Length > 0)
                        {
                            StringBuilder order = new StringBuilder();
                            order.Append("order by ");
                            foreach (var sf in table.Sorting.SortField)
                            {
                                order.Append(string.Format("[{0}]", sf.fieldname));
                                if (sf.order == SortOrder.desc)
                                    order.Append(" desc");
                                if (Array.IndexOf(table.Sorting.SortField, sf) != table.Sorting.SortField.Length - 1)
                                    order.Append(", ");
                            }
                            sw.WriteLine(Indent(d) + order);
                        }

                        sw.WriteLine(Indent(--d) + "END");
                        sw.WriteLine(Indent(d) + "GO");
                    }
                }
            }
        }

        public void CreateAllProcs(StreamWriter sw, int d)
        {
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.all))
            {

                string methodName = string.Format("All{0}", table.name);

                sw.WriteLine("SET ANSI_NULLS ON");
                sw.WriteLine("GO");
                sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                sw.WriteLine("GO");
                sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE [dbo].[{0}]", methodName));

                sw.WriteLine(Indent(--d) + "AS");
                sw.WriteLine(Indent(d++) + "BEGIN");
                sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                sw.WriteLine(Indent(d) + "select * from [{0}]", table.name);

                if (table.Sorting != null && table.Sorting.SortField.Length > 0)
                {
                    StringBuilder order = new StringBuilder();
                    order.Append("order by ");
                    foreach (var sf in table.Sorting.SortField)
                    {
                        order.Append(string.Format("[{0}]", sf.fieldname));
                        if (sf.order == SortOrder.desc)
                            order.Append(" desc");
                        if (Array.IndexOf(table.Sorting.SortField, sf) != table.Sorting.SortField.Length - 1)
                            order.Append(", ");
                    }
                    sw.WriteLine(Indent(d) + order);
                }

                sw.WriteLine(Indent(--d) + "END");
                sw.WriteLine(Indent(d) + "GO");


            }
        }

        public void CreateCustomProcs(StreamWriter sw, int d)
        {
            if (_ddl.Definition.Procedures != null)
            {
                foreach (var proc in _ddl.Definition.Procedures)
                {
                    sw.WriteLine("SET ANSI_NULLS ON");
                    sw.WriteLine("GO");
                    sw.WriteLine("SET QUOTED_IDENTIFIER ON");
                    sw.WriteLine("GO");
                    sw.WriteLine(Indent(d++) + string.Format("create PROCEDURE {0}", proc.FullyQualifiedSqlName));
                    if (proc.Input != null && proc.Input.Length > 0)
                    {
                        foreach (var f in proc.Input)
                        {
                            sw.Write(Indent(d) + "@{0} {1}", f.name, f.SqlDataType);
                            if (Array.IndexOf(proc.Input, f) != proc.Input.Count() - 1)
                                sw.WriteLine(",");
                            else
                                sw.WriteLine();
                        }
                    }
                    sw.WriteLine(Indent(--d) + "AS");
                    sw.WriteLine(Indent(d++) + "BEGIN");
                    sw.WriteLine(Indent(d) + "SET NOCOUNT ON;");
                    sw.WriteLine(Indent(d) + proc.Sql.Trim());
                    sw.WriteLine(Indent(--d) + "END");
                    sw.WriteLine(Indent(d) + "GO");
                }
            }
        }

        public static string WhereList(IEnumerable<Field> fields)
        {
            return fields.Where(f => !f.key).SepList(f => "[" + f.name + "] = @" + f.name, f => "and ");
        }

        public static string KeyList(TableDefinition table, string seperator)
        {
            return table.Field.Where(f => f.key).SepList(f => "[" + f.name + "] = @" + f.name, f => ", ");
        }

        public static string NonKeyList(TableDefinition table, string seperator)
        {
            return table.Field.Where(f => !f.key).SepList(f => "[" + f.name + "] = @" + f.name, f => ", ");
        }

        public static string Indent(int num)
        {
            if (num == 0)
                return string.Empty;
            return new string('\t', num);
        }

        public void CreateIndeces(StreamWriter sw, int d)
        {
            foreach (var table in DDL.Definition.Schema.Table.Where(x => x.Indexes != null && x.Indexes.Index != null))
            {
                foreach (var i in table.Indexes.Index)
                {
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Index [{0}]    Script Date: {1} ******/", i.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d) + string.Format("CREATE{0}{1} INDEX [{2}] on {3} ({4})"
                        , (i.unique) ? " UNIQUE" : ""
                        , i.type == Xml.IndexType.clustered ? " CLUSTERED" : " NONCLUSTERED"
                        , i.name
                        , table.FullyQualifiedSqlName
                        , i.IndexField.SepList(f => string.Format("[{0}]{1}", f.column, ((f.sort == Xml.SortOrder.desc) ? " desc" : "")), f => ", ")));
                    sw.WriteLine(Indent(d) + "GO");
                }
            }
        }

        public void DropIndeces(StreamWriter sw, int d)
        {
            foreach (var table in DDL.Definition.Schema.Table.Where(x => x.Indexes != null && x.Indexes.Index != null))
            {
                foreach (var i in table.Indexes.Index)
                {
                    sw.WriteLine(Indent(d) + string.Format("/****** Object:  Index [{0}]    Script Date: {1} ******/", i.name, DateTime.Now.ToString("ddd MM yyy HH:mm:ss")));
                    sw.WriteLine(Indent(d) + string.Format("IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = '{0}')",i.name));
                    sw.WriteLine(Indent(d) + string.Format("DROP INDEX {0}.[{1}]", table.FullyQualifiedSqlName, i.name));
                    sw.WriteLine(Indent(d) + "GO");
                }
            }
        }
    }
}
